﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

using System.Data.SqlClient;

namespace MyNet.WebServices {

    using MyNet.WebServices.DataModel;

    public class MyNetDbService : IMyNetDbService {
        
        protected static String databaseServer = "<>";
        protected static String databaseName = "<>";
        protected static String username = "<>";
        protected static String password = "<>";


        static String MyNetDbConnectionString;

        public MyNetDbService () {

            // Create DB Connection String
            var connectionString = new SqlConnectionStringBuilder {
                DataSource = databaseServer,
                InitialCatalog = databaseName,
                Encrypt = true,
                TrustServerCertificate = false,
                UserID = username,
                Password = password
            };

            MyNetDbConnectionString = connectionString.ToString ();

        } // Constructor


        /// Add Contact
        public void addContact ( Contact contact ) {

            // Connect to MyNetDB
            using ( var dbConnection = new SqlConnection ( MyNetDbConnectionString ) ) {

                dbConnection.Open ();

                // Execute SQL statements
                using ( SqlCommand sqlCommand = dbConnection.CreateCommand () ) {

                    // Firstname and Lastname may contain apostrophes '
                    // They are replaced with double apostrophes '' before insertion in DB
                    var firstname = contact.Firstname.Replace ( "'", "''" );
                    var lastname = contact.Lastname.Replace ( "'", "''" );

                    var sqlStatementTemplate =
                        "INSERT INTO mynet.Contact ( Firstname, Lastname ) VALUES ( N'{0}', N'{1}')";
                    var sqlStatement = String.Format ( sqlStatementTemplate, firstname, lastname );

                    // Insert Contact in DB
                    sqlCommand.CommandText = sqlStatement;
                    sqlCommand.ExecuteNonQuery ();

                } // SqlCommand                

                dbConnection.Close ();

            } // dbConnection

        } // Method


        /// Get All Contacts
        public List<Contact> getContacts () {

            List<Contact> contacts = null;

            // Connect to MyNetDB
            using ( var dbConnection = new SqlConnection ( MyNetDbConnectionString ) ) {

                dbConnection.Open ();

                // Execute SQL statements
                using ( SqlCommand sqlCommand = dbConnection.CreateCommand () ) {

                    // Query: Select ALL Contacts
                    sqlCommand.CommandText = "SELECT * FROM mynet.Contact";
                    SqlDataReader reader = sqlCommand.ExecuteReader ();

                    // Create Contact object for every contact in MyNetDB
                    contacts = new List<Contact> ();

                    while ( reader.Read () ) {

                        var contact = new Contact {
                            Id = (int) reader["Id"],
                            Firstname = (String) reader["Firstname"],
                            Lastname = (String) reader["Lastname"]
                        };

                        contacts.Add ( contact );

                    } // While

                } // SqlCommand                

                dbConnection.Close ();

            } // dbConnection

            return contacts;

        } // Method

    } // Class

}
